Otimizando operações DML com o uso de FORALL
Por Daniela Petruzalek,
Postado em julho 2012
Introdução
No artigo anterior apresentamos o fenômeno de troca de contextos e o seu impacto no tempo de execução de querys e blocos PL/SQL. O objetivo deste artigo é expandir este tema, tratando das trocas de contexto relativas a operações DML em blocos PL/SQL e as respectivas técnicas para sua otimização.
Conceitos básicos
A troca de contexto é o fenômeno de chaveamento entre a engine SQL e a engine PL/SQL durante a execução de querys ou blocos PL/SQL. Este é um mecanismo constitutivo do banco de dados Oracle e não pode ser desligado, mas existem diversas técnicas que podem ser empregadas para minimizar o seu impacto.
Estamos invocando uma troca de contexto sempre que, dentro de um bloco PL/SQL, executamos uma query ou um comando DML (INSERT, UPDATE ou DELETE). Se executados isoladamente, estes comandos costumam ter pouco ou mesmo nenhum impacto na performance mas, quando executados em loops, o efeito das trocas de contexto se torna cumulativo e o prejuízo na performance do procedimento se torna bem evidente.
Pensando nisso, a Oracle desenvolveu o comando FORALL, cujo objetivo é enviar ao servidor um lote de comandos DML de uma única vez, utilizando apenas uma troca de contexto. O resultado dessa abordagem são procedimentos muito mais leves, rápidos e eficientes. A seguir veremos como converter um código que utiliza comandos DML dentro de loops para uma abordagem utilizando o FORALL.
Utilizando o FORALL para reduzir as trocas de contexto de DML
No nosso dia a dia é muito comum encontrarmos códigos para processamento de dados que seguem o seguinte formato:
SQL> declare
2 y number;
3
4 begin
5 -- Abre um cursor loop na tabela de origem
6 for r in (select x from t)
7 loop
8 -- Faz o processamento dos dados
9 y := r.x * r.x;
10 -- Insere os dados na tabela de destino
11 insert into u values(y);
12 end loop;
13 end;
14 / O problema desta abordagem é que, ao codificar o procedimento com um loop e fazendo os INSERTs um a um o Oracle vai ser forçado a executar uma troca de contexto para cada comando DML em cada iteração do loop.
Quando se tratam de poucos registros na tabela de origem é possível que o impacto das trocas de contexto seja insignificante. Mas, a medida que a massa de dados aumenta, o efeito se torna cumulativo e podemos incorrer em problemas seríssimos de performance. Aliás, é muito comum problemas deste tipo aparecerem apenas nos ambientes de produção, especialmente quando no ambiente de teste a validação da rotina é feita com um conjunto de dados muito menor do que o esperado para o processo real. Por este motivo é importante ter a performance como uma questão de design desde os primeiros momentos do projeto, evitando surpresas para quando o sistema entrar em produção.
A maneira mais adequada de endereçar este tipo de procedimento é sempre que possível executar o processamento em um único passo, como por exemplo, utilizando um INSERT SELECT com a hint APPEND. Quando isso não é possível e precisamos fazer uso do PL/SQL para quebrar o processamento em passos menores, o uso do FORALL é altamente indicado. Vamos ver como funciona este comando a seguir:
SQL> declare
2 y dbms_utility.number_array;
3
4 begin
5 -- Abre um cursor loop na tabela de origem
6 for r in (select x from t)
7 loop
8 -- Processa os dados e guarda o resultado no array
9 y(y.count() + 1) := r.x * r.x; 10 end loop;
11 -- Insere os dados processados todos de uma vez, em um único comando DML
12 forall i in y.first .. y.last
13 insert into u values(y(i));
14 end;
15 / Note que a principal diferença é a troca da variável y por um array de number ao invés de utilizar uma variável discreta. Isto permitiu que o loop de processamento armazenasse os resultados no array ao invés de gravar diretamente cada linha na tabela e, com isso, a única operação DML necessária foi movida para fora do loop. Finalmente, o comando FORALL processa o array e submete para o banco todos os comandos DML necessários em um único passo e, portanto, em apenas uma troca de contexto.
A seguir veremos um exemplo um pouco mais complexo para demonstrar o ganho de performance que pode ser obtido com essa abordagem.
Primeiro, vamos começar criando uma massa de dados para servir como nossa tabela de origem. O código abaixo irá criar uma tabela com 100 cópias da view all_objects para atingir uma massa de aproximadamente 1 milhão de registros:
SQL> drop table t;
Table dropped.
SQL> create table t as
2 select object_id, owner, object_name
3 from all_objects,
4 (select rownum from dual
5 connect by level <= 100);
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
1201600
SQL> desc t
Name Null? Type
--------------------------- -------- -------------
OBJECT_ID NOT NULL NUMBER
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SQL> drop table u;
Table dropped.
SQL> create table u(object_id number, full_name varchar2(70));
Table created.
Vamos agora executar os dois tipos de processamento: insert um a um e o insert em massa com FORALL e comparar o tempo de execução de cada bloco. Começando pelo insert um a um:
SQL> set serveroutput on SQL> declare
2 t0 number := dbms_utility.get_time();
3 full_name varchar2(70);
4
5 begin
6 for r in (select object_id, owner, object_name from t)
7 loop
8 full_name := r.owner || '.' || r.object_name;
9 insert into u values(r.object_id, full_name);
10 end loop;
11 commit;
12 dbms_output.put_line('Tempo: ' || ((dbms_utility.get_time - t0) / 100) || ' segundos');
13 end;
14 / Tempo: 155.09 segundos
PL/SQL procedure successfully completed. Processando um insert por linha levou quase três minutos para processar os cerca de 1,2 milhões de registros. Agora, vamos adaptar o código para fazer o mesmo processamento usando o comando FORALL para reduzir as trocas de contexto:
SQL> declare
2 t0 number := dbms_utility.get_time;
3 v_idx number := 1;
4 type t_rec is record(
5 object_id number,
6 full_name varchar2(70));
7 type t_rec_array is table of t_rec index by pls_integer;
8 a_rec t_rec_array;
9
10 begin
11 for r in (select object_id, owner, object_name from t)
12 loop
13 a_rec(v_idx).object_id := r.object_id;
14 a_rec(v_idx).full_name := r.owner || '.' || r.object_name;
15 v_idx := v_idx + 1;
16 if (mod(v_idx, 100) = 0) then
17 forall i in a_rec.first .. a_rec.last
18 insert into u values a_rec(i);
19 a_rec.delete;
20 v_idx := 1;
21 end if;
22 end loop;
23 if a_rec.first is not null then
24 forall i in a_rec.first .. a_rec.last
25 insert into u values a_rec(i);
26 end if;
27 commit;
28 dbms_output.put_line('Tempo: ' || ((dbms_utility.get_time - t0) / 100) || ' segundos');
29 end;
30 / Tempo: 21.34 segundos
PL/SQL procedure successfully completed. Como vocês podem perceber a diferença é impressionante. O tempo de processamento caiu de 155.09 segundos para apenas 21.34 segundos, ou seja, o processamento utilizando FORALL foi mais de 7 vezes mais rápido.
Observe, contudo, que o código foi modificado para comportar o processamento por arrays em dois aspectos principais: primeiro, foi criado um record no formato dos dados a serem gravados e, posteriormente, um array deste record para servir de base para o comando FORALL; e, em segundo lugar, foi criado um controle adicional para que fosse executado um FORALL para cada 100 registros processados.
O motivo para subdividir a carga em múltiplos de 100 está relacionado ao uso de arrays. Lembre-se que arrays são estruturas de memória que consomem PGA. Fazer a carga de 1.2 milhões de registros em um array pode causar sérias consequências em um servidor, ainda mais se estivermos falando de um record com muitas colunas ou cujas colunas possuam tipos de dados muito grande, como por exemplo, um VARCHAR2(4000).
Portanto, é uma boa prática quando trabalhamos com arrays utilizar limites de 100 ou até mesmo 1000 registros, mas não muito mais do que isso para não correr o risco de utilizar muita memória do servidor para uma única sessão.
Conclusão
Sempre que possível devemos executar operações de carga em um único passo, como por exemplo, utilizando um INSERT SELECT com a hint APPEND. Mas quando isso não é possível, e precisamos recorrer ao processamento com PL/SQL, o uso do comando FORALL é fundamental para a otimização de trocas de contexto por DML. Vimos no exemplo acima uma melhora de mais de 7 vezes no tempo de execução se comparado com a abordagem tradicional. Na minha prática, já tive oportunidade de testemunhar ganhos ainda maiores em código de produção. Portanto, o FORALL é uma ferramenta indispensável de otimização, tanto para desenvolvedores como DBAs.
Note que neste artigo tratei apenas a forma básica do uso deste comando. Para quem quiser conhecer as outras formas de trabalhar com FORALL, recomendo a leitura deste tópico no manual de referência do PL/SQL:
//docs.oracle.com/cd/E11882_01/appdev.112/e17126/forall_statement.htm
Postado por Daniela Petruzalek. Oracle Advanced PL/SQL Developer Certified Professional, Oracle PL/SQL Developer Certified Associate, Oracle Database 11g Administrator Certified Associate, Oracle Database SQL Certified Expert, OPN Certified Specialist.